using System;
using System.Data;
using System.Data.SqlClient;

namespace CSP.SimpleCMS.Data
{
	/// <summary>
	/// Data access class
	/// The primary function of the class is to abstract
	/// data access code from the business logic components.
	/// </summary>
	public sealed class DataAccess : IDisposable 
	{	
		//-----------------------------------------------------------------------
		// Constants and Enumerations
		//-----------------------------------------------------------------------
		private const int		SQL_COMMAND_TIME_OUT			= 300;
		private const string	AT_SYMBOL						= "@";

		//-----------------------------------------------------------------------
		// Member Variables
		//-----------------------------------------------------------------------
		private string				m_connectionString	= String.Empty;
		private SqlConnection		m_connection		= null;
		private SqlCommand			m_command			= null;
		private SqlTransaction		m_transaction		= null;

		//-----------------------------------------------------------------------
		// Public Methods
		//-----------------------------------------------------------------------
		public DataAccess()
		{
			m_connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
		}

		/// <summary>
		/// Method used to execute stored procedure and 
		/// return no results.  
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="sqlparams">Optional array of SqlParameters.  Use null if no parameters</param>
		public void ExecSP(string procName, SqlParameter[] sqlparams)
		{			
			SqlCommand cmd = CreateSqlCommand();
			cmd.Connection = this.Connection;
			cmd.CommandText = procName;
			
			//Add Each parameter to command object
			if(sqlparams!=null)
			{
				for(int i=0; i<sqlparams.Length; i++)
				{
					cmd.Parameters.Add(sqlparams[i]);
				}
			}

			cmd.ExecuteNonQuery();

			//Flag objects for GC
			cmd.Dispose();
		}

		/// <summary>
		/// Method used to execute stored procedure and 
		/// return no results.  
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="parameterName">Name of stored procedure parameter</param>
		/// <param name="parameterName">Value of stored procedure parameter</param>
		public void ExecSP(string procName,  string parameterName, object objectID)
		{
			
			SqlCommand cmd = CreateSqlCommand();
			cmd.Connection = this.Connection;
			cmd.CommandText = procName;
			
			//Add parameter to command object
			TranslateParamName(ref parameterName);
			SqlParameter param = new SqlParameter(parameterName,objectID);
			cmd.Parameters.Add(param);

			cmd.ExecuteNonQuery();

			//Flag objects for GC
			cmd.Dispose();
		}

		/// <summary>
		/// Method used to execute stored procedure and
		/// return the integer return value of the stored procedure to the user
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="sqlparams">Optional array of SqlParameters.  Use null if no parameters</param>
		public int ExecSPReturnInt(string procName, SqlParameter[] sqlparams)
		{
			SqlCommand cmd = CreateSqlCommand();
			cmd.CommandText=procName;

			//Add Return Value
			SqlParameter p = new SqlParameter("@returnID",SqlDbType.Int);
			p.Direction=ParameterDirection.ReturnValue;
			cmd.Parameters.Add(p);

			//Add Each parameter to command object
			if(sqlparams!=null)
			{
				for(int i=0; i<sqlparams.Length; i++)
				{
					cmd.Parameters.Add(sqlparams[i]);
				}
			}

			cmd.ExecuteNonQuery();
			
			//Set Return Value
			int returnValue = (int)cmd.Parameters[0].Value;

			//Flag objects for GC
			cmd.Dispose();

			return(returnValue);
		}

		/// <summary>
		/// Method used to execute stored procedure and 
		/// return a dataset.  
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="sql">Sql command text</param>
		public DataSet ExecSPReturnDS(string procName, params SqlParameter[] parameters)
		{
			DataSet ds = new DataSet();
			
			SqlCommand cmd = CreateSqlCommand();
			SqlDataAdapter adapter = new SqlDataAdapter();
			cmd.CommandText = procName;

			// Add Each parameter to command object
			if(parameters != null)
				foreach(SqlParameter parameter in parameters)
				{
					cmd.Parameters.Add(parameter);
				}

			adapter.SelectCommand = cmd;
			adapter.Fill(ds);
			
			// Close objects and flag for GC
			adapter.SelectCommand.Dispose();
			adapter.Dispose();

			return(ds);
		}

		/// <summary>
		/// Method used to execute stored procedure with a 
		/// single integer parameter
		/// </summary>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="procName">Name of parameter</param>
		/// <param name="sql">Value of Parameter</param>
		public DataSet ExecSPReturnDS(string procName, string parameterName, object objectID)
		{
			TranslateParamName(ref parameterName);
			return ExecSPReturnDS(procName, new SqlParameter(parameterName,objectID));
		}

		/// <summary>
		/// Method used to execute stored procedure and 
		/// return a dataset.  
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="sql">Sql command text</param>
		public SqlDataReader ExecSPReturnReader(string procName, params SqlParameter[] sqlparams)
		{			
			SqlCommand cmd = CreateSqlCommand();
			cmd.CommandText=procName;

			//Add Each parameter to command object
			if (sqlparams != null)
			{
				for(int i=0; i<sqlparams.Length; i++)
				{
					cmd.Parameters.Add(sqlparams[i]);
				}
			}

			return cmd.ExecuteReader();
		}

		/// <summary>
		/// Method used to execute arbitrary SQL String and 
		/// return a scalar.  The scalar is the first column
		/// of the first row of the rowset returned by the sp  
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="procName">Name of stored procedure to execute</param>
		/// <param name="sql">Sql command text</param>
		public object ExecSPReturnScalar(string procName, params SqlParameter[] sqlparams)
		{	
			SqlCommand cmd = CreateSqlCommand();
			cmd.CommandText = procName;
			
			//Add Each parameter to command object
			if(sqlparams!=null)
			{
				for(int i=0; i<sqlparams.Length; i++)
				{
					cmd.Parameters.Add(sqlparams[i]);
				}
			}
			
			object obj = cmd.ExecuteScalar();			
			
			//Flag objects for GC
			cmd.Dispose();
			return(obj);
		}

		/// <summary>
		/// Method used to execute arbitrary SQL String and 
		/// return no results.  Often used for update, insert, 
		/// or delete statements or stored procs that return no
		/// results.
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="sql">Sql command text</param>
		public void ExecSQL(string sql)
		{			
			SqlCommand cmd = CreateSqlCommand(sql);
			cmd.ExecuteNonQuery();
			
			//Flag object for GC
			cmd.Dispose();
		}

		/// <summary>
		/// Execute arbitrary sql text statement and return a dataset
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="sql">Sql command text</param>
		/// <returns>DataSet of results of sql text</returns>
		public DataSet ExecSQLReturnDS(string sql)
		{
			DataSet oDS = new DataSet();
			
			SqlDataAdapter adapter = new SqlDataAdapter();
			adapter.SelectCommand = CreateSqlCommand(sql);
			adapter.Fill(oDS);
			adapter.SelectCommand.Dispose();
			adapter.Dispose();
			return oDS;
		}

		/// <summary>
		/// Execute arbitrary SQL text and return a SQLDataReader
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="sql">SQL Text to execute</param>
		/// <returns>SqlDataReader of results</returns>
		public SqlDataReader ExecSQLReturnReader(string sql)
		{
			SqlCommand cmd = CreateSqlCommand(sql);
			return cmd.ExecuteReader();
		}

		/// <summary>
		/// Execute a SQL statement that returns a scalar value
		/// </summary>
		/// <param name="conn">Open SqlConnection</param>
		/// <param name="sql">Sql command text</param>
		/// <returns></returns>
		public object ExecSQLReturnScalar(string sql)
		{
			
			SqlCommand cmd = CreateSqlCommand(sql);
			object obj = cmd.ExecuteScalar();
			
			
			cmd.Dispose();
			return(obj);
		}

		/// <summary>
		/// Make input Stored Procedure Parameter.
		/// </summary>
		public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) 
		{
			return MakeDbParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
		}

		/// <summary>
		/// Make Output Stored Procedure Parameter.
		/// </summary>
		public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) 
		{
			return MakeDbParam(ParamName, DbType, Size, ParameterDirection.Output, null);
		}		

		/// <summary>
		/// Make Input/Output Stored Procedure Parameter.
		/// </summary>
		public SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, object Value) 
		{
			return MakeDbParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
		}

		/// <summary>
		/// Close the database connection.
		/// </summary>
		public void CloseConnection() 
		{
			if (m_connection != null)
			{
				m_connection.Close();
			}
		}

		/// <summary>
		/// 
		/// </summary>
		public void BeginTransaction()
		{
			m_transaction = this.Connection.BeginTransaction();
		}

		/// <summary>
		/// 
		/// </summary>
		public void CommitTransaction()
		{
			m_transaction.Commit();
			m_transaction = null;
		}

		/// <summary>
		/// 
		/// </summary>
		public void RollBackTransaction()
		{
			m_transaction.Rollback();
		}

		/// <summary>
		/// Release resources.
		/// </summary>
		public void Dispose() 
		{
			// make sure connection is closed
			if (m_connection != null) 
			{
				// Close the connection
				if(m_connection.State != ConnectionState.Closed)
					m_connection.Close();

				m_connection.Dispose();
				m_connection = null;
			}	
			
			if(m_transaction != null)
			{
				//???? m_transaction.Rollback();
				m_transaction.Dispose();
				m_transaction = null;
			}
		}


		//-----------------------------------------------------------------------
		// Private Methods
		//-----------------------------------------------------------------------
		/// <summary>
		/// Make stored procedure param.
		/// </summary>
		private SqlParameter MakeDbParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) 
		{
			SqlParameter param;			
			TranslateParamName(ref ParamName);

			if(Size > 0)
				param = new SqlParameter(ParamName, DbType, Size);
			else
				param = new SqlParameter(ParamName, DbType);

			param.Direction = Direction;
			if (!(Direction == ParameterDirection.Output && Value == null))
				param.Value = Value;

			return param;
		}

		/// <summary>
		/// Open the database connection.
		/// </summary>
		private void Open() 
		{
			// open connection
			if (m_connection == null) 
			{
				m_connection = new SqlConnection(m_connectionString);
				m_connection.Open();
			}				
		}

		/// <summary>
		/// Creates or leverages an existing Sql Command Object.
		/// Also encapsulates the use of transactions.
		/// </summary>
		/// <returns></returns>
		private SqlCommand GetSqlCommand()
		{
			if(m_command == null)
			{
				m_command = new SqlCommand();
				m_command.CommandTimeout = SQL_COMMAND_TIME_OUT;
			}
			else
			{
				// Clear Command Object
				m_command.Parameters.Clear();
				m_command.CommandText = String.Empty;				
			}
			m_command.Connection = this.Connection;
			m_command.CommandType = CommandType.StoredProcedure;
			if(m_transaction != null)
				m_command.Transaction = m_transaction;			
			return m_command;
		}

		/// <summary>
		/// Encapsulates the creation of the command object.
		/// Calls through to GetSqlCommand in order to leverage
		/// the existing command object
		/// </summary>
		/// <returns></returns>
		private SqlCommand CreateSqlCommand()
		{
			return GetSqlCommand();
		}

		/// <summary>
		/// Encapsulates the creation of the command object.
		/// Calls through to GetSqlCommand in order to leverage
		/// the existing command object
		/// </summary>
		/// <param name="cmdText"></param>
		/// <returns></returns>
		private SqlCommand CreateSqlCommand(string cmdText)
		{
			SqlCommand sc = GetSqlCommand();
			sc.CommandText = cmdText;
			m_command.CommandType = CommandType.Text;
			return sc;
		}

		/// <summary>
		/// Translates the Parameter name to include
		/// implementation specific characters
		/// </summary>
		/// <param name="ParamName"></param>
		private void TranslateParamName(ref string ParamName)
		{
			
			// Accommodate @ Symbol
			if(ParamName.Substring(0, 1) != AT_SYMBOL)
				ParamName = ParamName.Insert(0, AT_SYMBOL);
		}

		//-----------------------------------------------------------------------
		// Properties
		//-----------------------------------------------------------------------
		
		/// <summary>
		/// Connection String to Database
		/// </summary>
		public string ConnectionString
		{
			get { return m_connectionString; }
			set { m_connectionString = value; }
		}

		/// <summary>
		/// Connection Object
		/// </summary>
		public SqlConnection Connection
		{
			get { 
					Open();
					return m_connection; 
			}
			set { m_connection = (SqlConnection)value; }
		}
	}
}
